2023 QA/QC

Published

February 28, 2024

Introduction

Prior to publishing analysis and interpretation of water quality data, we will ensure that all data that meets QA/QC standards outlined in the current project Quality Assurance Project Plan (QAPP) and is accessible in the appropriate public repository.

Water quality data from this project is ultimately destined for public archival in the Environmental Protection Agency’s Water Quality Exchange (EPA WQX).

The QAPP for this project describes data management details and responsible parties for each step of the data pipeline from observation to repository. The 2023 data preparation and review process is published here.

Year 2023 Water Quality Data

In this chapter we will collate 2023 laboratory data from several sources into a single spreadsheet document with a consistent format. The desired end format is a spreadsheet template provided by the EPA Water Quality Exchange. These template files are available to download from the EPA at https://www.epa.gov/waterdata/water-quality-exchange-web-template-files.

Once the data is collated, it will be evaluated according to a Quality Assurance Checklist (template example provided by the Alaska Department of Environmental Conservation Soldotna office). Field observations that do not meet the quality assurance standards described in the evaluation checklist will be flagged as “Rejected” before being uploaded to the EPA WQX.

Data that has been uploaded to the EPA WQX is evaluated biannually by the Alaska Department of Environmental Conservation (ADEC) in their Integrated Water Quality Monitoring and Assessment Report ¹. The integrated report evaluates available water quality data from the previous five years against Alaska water quality standards and regulations (adec2020?).

2023 Water Quality Data Formatting

The code scripts in this document assemble water quality data from the two analytical laboratories that partnered with Kenai Watershed Forum for this project in 2023:

SGS Laboratories (Anchorage, AK)
Soldotna Wastewater Treatment Plant (Soldotna, AK)

2023 Metals/Nutrients Lab Results (SGS Labs)

Download Original Spring 2023 Metals/Nutrients Lab Results from SGS - Electronic Data Delivery file

Download Original Spring 2023 Metals/Nutrients Lab Results from SGS - PDF file

Download Original Summer 2023 Metals/Nutrients Lab Results from SGS - Electronic Data Delivery file

Download Original Summer 2023 Metals/Nutrients Lab Results from SGS - PDF file

Download Original Summer 2023 Metals/Nutrients Chain of Custody docs from SGS - PDF file

–> use EPA metals guidance in QA/QC rather than at this stage!

check all these again, make decision if static, concatenated, or formulaic

general strategy: get all columns of sgs data to conform to final CDX format before joining. (e.g. do all sgs first, then swwtp)

there are pieces and parts existing in the 2021 script, but to stay organized it would make sense to proceed L –> R for each column

2023 Fecal Coliform Lab Results (Soldotna Wastewater Treatment Plant Lab)

Chain of custody documents available upon request.

value for "which" not specified, defaulting to c("rows", "cols")

Joining with `by = join_by(sample_location_rm)`

2023 Total Suspended Solids Lab Results (Soldotna Wastewater Treatment Plant (SWWTP))

Download Original Spring 2023 Total Suspended Solids Results from SWWTP

Download Original Summer 2023 Total Suspended Solids Results from SWWTP

Chain of custody documents available upon request

New names:
New names:
• `` -> `...8`

Warning: There was 1 warning in `mutate()`.
ℹ In argument: `time_sampled = format(...)`.
Caused by warning in `as.POSIXct()`:
! NAs introduced by coercion

value for "which" not specified, defaulting to c("rows", "cols")

Joining with `by = join_by(sample_location)`

Warning: Unknown columns: `project_id`, `activity_media_name`,
`activity_media_subdivision_name`, `activity_start_time_zone`,
`activity_latitude`, `activity_longitude`, `activity_depth_height_measure`,
`activity_depth_height_unit`, `activity_comment`,
`activity_horizontal_collection_method`, `activity_horizontal_reference_datum`,
`result_weight_basis`, `result_value_type`, `sample_collection_equipment_name`,
`result_status_id`, `laboratory_comment_text`, `thermal_preservative_used`

Warning: Unknown columns: `project_id`, `activity_media_name`,
`activity_media_subdivision_name`, `activity_start_date`,
`activity_start_time_zone`, `activity_latitude`, `activity_longitude`,
`activity_depth_height_measure`, `activity_depth_height_unit`,
`activity_comment`, `activity_horizontal_collection_method`,
`activity_horizontal_reference_datum`, `result_weight_basis`,
`method_speciation`, `result_value_type`, `sample_collection_equipment_name`,
`result_status_id`, `laboratory_comment_text`, `thermal_preservative_used`

Warning: Unknown columns: `project_id`, `activity_media_name`,
`activity_media_subdivision_name`, `activity_start_time_zone`,
`activity_latitude`, `activity_longitude`, `activity_depth_height_measure`,
`activity_depth_height_unit`, `activity_comment`,
`activity_horizontal_collection_method`, `activity_horizontal_reference_datum`,
`result_weight_basis`, `method_speciation`, `result_value_type`,
`sample_collection_equipment_name`, `result_status_id`,
`laboratory_comment_text`, `thermal_preservative_used`

Rows: 22 Columns: 3
── Column specification ────────────────────────────────────────────────────────
Delimiter: ","
dbl (3): monitoring_location_id, activity_latitude, activity_longitude

ℹ Use `spec()` to retrieve the full column specification for this data.
ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
Joining with `by = join_by(monitoring_location_id)`

2023 Data QA/QC Evaluation

Prior to upload to the EPA WQX, all water quality data must be checked against a series of standard questions in order to evaluate how quality assurance / quality check (QA/QC) requirements are met. The draft Data Evaluation Checklist Template (available for download below) outlines these questions:

Download 2023 Draft Kenai Baseline Data Evaluation Checklist Template

Download results pre-QA/QC; including lab QA QC info

Pre-database

Overall project success

1.) Were the appropriate analytical methods used for all parameters?

Yes. Analytical methods from the approved 2020 QAPP were employed.

2.) Were there any deviations from the sampling plan?

Yes. One site in spring 2023 could not be accessed for sampling due to low water conditions (Kenai River main stem, RM 10.1).

3.) Were field duplicates, blanks, and/or other QC samples collected as planned?

https://dec.alaska.gov/water/water-quality/integrated-report/↩︎

# 2023 QA/QC {.unnumbered} --- execute: echo: false date: "`r Sys.Date()`" format: html: code-fold: true code-tools: true code-summary: "Show the code" --- ```{r, 2023 WQX formatting for SGS, echo = F, message = F} #| warning: false #| message: false # clear environment rm(list=ls()) # load packages library(tidyverse) library(readxl) library(openxlsx) library(data.table) library(stringr) library(magrittr) library(janitor) library(hms) library(lubridate) library(anytime) library(stringi) xfun::pkg_load2(c("htmltools", "mime")) # Assign 2023 Field Sample Dates # Spring 2023 sampling date spring23_sample_date <- ymd("2023-05-02") # Summer 2023 Sampling Date summer23_sample_date <- ymd("2023-07-18") ``` ## Introduction Prior to publishing analysis and interpretation of water quality data, we will ensure that all data that meets QA/QC standards outlined in the current project [Quality Assurance Project Plan (QAPP)](https://www.kenaiwatershed.org/news-media/qapp-revisions-completed-2023/) and is accessible in the appropriate public repository. Water quality data from this project is ultimately destined for public archival in the Environmental Protection Agency's Water Quality Exchange (EPA WQX). The QAPP for this project describes data management details and responsible parties for each step of the data pipeline from observation to repository. The 2023 data preparation and review process is published here. ### Year 2023 Water Quality Data In this chapter we will collate 2023 laboratory data from several sources into a single spreadsheet document with a consistent format. The desired end format is a spreadsheet template provided by the EPA Water Quality Exchange. These template files are available to download from the EPA at <https://www.epa.gov/waterdata/water-quality-exchange-web-template-files>. Once the data is collated, it will be evaluated according to a Quality Assurance Checklist (template example provided by the Alaska Department of Environmental Conservation Soldotna office). Field observations that do not meet the quality assurance standards described in the evaluation checklist will be flagged as "Rejected" before being uploaded to the EPA WQX. Data that has been uploaded to the EPA WQX is evaluated biannually by the Alaska Department of Environmental Conservation (ADEC) in their [Integrated Water Quality Monitoring and Assessment Report](https://dec.alaska.gov/water/water-quality/integrated-report/)[^2023-1]. The integrated report evaluates available water quality data from the previous five years against Alaska water quality standards and regulations [@adec2020]. [^2023-1]: https://dec.alaska.gov/water/water-quality/integrated-report/ #### 2023 Water Quality Data Formatting The code scripts in this document assemble water quality data from the two analytical laboratories that partnered with Kenai Watershed Forum for this project in 2023: - SGS Laboratories (Anchorage, AK) - Soldotna Wastewater Treatment Plant (Soldotna, AK) ------------------------------------------------------------------------ ##### 2023 Metals/Nutrients Lab Results (SGS Labs) ```{r, echo = F, message = F} library("xfun") xfun::embed_file('other/input/2023/spring_2023_wqx_data/Data/SGS/Revision 1 SGS Data/1231846_FC5912_Rev1.csv', text = "Download Original Spring 2023 Metals/Nutrients Lab Results from SGS - Electronic Data Delivery file") xfun::embed_file('other/input/2023/spring_2023_wqx_data/Data/SGS/Revision 1 SGS Data/SGS_Spring_2023_data_final.pdf', text = "Download Original Spring 2023 Metals/Nutrients Lab Results from SGS - PDF file") ``` ```{r, echo = F} xfun::embed_file('other/input/2023/summer_2023_wqx_data/Data/SGS/Summer 2023 SGS Agency Baseline.xlsx', text = "Download Original Summer 2023 Metals/Nutrients Lab Results from SGS - Electronic Data Delivery file") xfun::embed_file('other/input/2023/summer_2023_wqx_data/Data/SGS/1233640.pdf', text = "Download Original Summer 2023 Metals/Nutrients Lab Results from SGS - PDF file") xfun::embed_file('other/input/2023/summer_2023_wqx_data/Data/SGS/1233640_COC.pdf', text = "Download Original Summer 2023 Metals/Nutrients Chain of Custody docs from SGS - PDF file") ``` ```{r message = FALSE, echo = F, include = F} #| warning: false #| message: false ############################ Part A.1: SGS 2023 Data Read In ############################# ## Reformat SGS data downloaded from their server client (SGS Engage, full EDD files) to match desired template # read in # address column type issues such that both dataframes can be merged spring_batch_sgs23 <- read.csv("other/input/2023/spring_2023_wqx_data/Data/SGS/Revision 1 SGS Data/1231846_FC5912_Rev1.csv") %>% select(-PROJECT_ID,-DISSOLVED) summer_batch_sgs23 <- read_excel("other/input/2023/summer_2023_wqx_data/Data/SGS/Summer 2023 SGS Agency Baseline.xlsx", sheet = "Sheet9") %>% select(-PROJECT_ID,-DISSOLVED) # later, need to address different categorical results that were in removed "DISSOLVED" column: #> unique(spring_batch_sgs23$DISSOLVED) #[1] TRUE #> unique(summer_batch_sgs23$DISSOLVED) #[1] "." "L" "T" # joining and preparatory steps ## clean up column names and bind seasons together sgs23 <- bind_rows(spring_batch_sgs23,summer_batch_sgs23) %>% clean_names() %>% remove_empty() %>% # add lab name mutate(lab_name = "SGS North America, Anchorage, Alaska") %>% # make 'matrix' column consistent select(-matrix) %>% mutate(matrix = "Water") %>% # prepare separate time and date columns for # - sample collection # - lab receipt # - lab run # - extraction # NOTE summer 2023 lab receipt time/dates missing ?!@ transform(collect_date = mdy_hm(collect_date), rec_date = mdy_hm(rec_date), run_date = mdy_hm(run_date), extracted_date = mdy_hm(extracted_date)) %>% separate(collect_date, sep = " ", into = c("collect_date","collect_time")) %>% separate(rec_date, sep = " ", into = c("rec_date","rec_time")) %>% separate(run_date, sep = " ", into = c("run_date","run_time")) %>% separate(extracted_date, sep = " ", into = c("extracted_date","extracted_time")) # NOTE: in some past years, total metals analyses (method 200.7) have been subcontracted to ALS Laboratories (Seattle). In 2023, SGS Anchorage had the 200.7 methods run by SGS Orlando. Thus, these results are already read in here. # remove individual dataframe rm(spring_batch_sgs23,summer_batch_sgs23) ################### Part A.2: Create Consistent Sample Location Names for SGS data ##################### # export list of unique sample_id sgs_sitenames <- sgs23 %>% distinct(sample_id,collect_date) colnames(sgs_sitenames) <- c("sample_id","collect_date") sgs_sitenames %>% arrange(sample_id) write.csv(sgs_sitenames,"other/input/2023/misc/site_names.csv", row.names = F) # manually edit site names csv (external to script) # read in manually edited csv sgs_sitenames <- read_csv("other/input/2023/misc/site_names_manual_edit_sgs23.csv") %>% filter(!is.na(site_id)) %>% # remove trailing spaces mutate(sample_id = str_trim(sample_id, side = "right")) %>% rename(`site_id ` = site_id) colnames(sgs_sitenames) <- c("sample_id","site_id") #write.csv(sgs_sitenames, "other/input/2023/misc/site_names_manual_edit.csv", row.names = F) # join site_id to main dataframe sgs23 <- left_join(sgs23,sgs_sitenames, by = "sample_id") # NOTE: the join for the "No Name Creek" sites are not cooperating. # After investigating possible reasons, (white spaces, etc) no diagnoses are evident. # For time efficiency, we will instead manually rectify this in script with mutate and case_when sgs23 %<>% mutate(site_id = case_when( grepl("No Name|NO NAME", sample_id) ~ "KBL_t_00.0", TRUE ~ site_id)) # join "monitoring location id" to overall sgs23 dataframe # import example data w/ monitoring location IDs location_ids <- read.csv("other/output/example_output/results_activities_2021.csv") %>% clean_names() %>% select(monitoring_location_id, activity_id) %>% separate(activity_id, sep = "-", into = "site_id") %>% distinct() # join sgs23 <- left_join(sgs23,location_ids, by = "site_id") # NOTE: the join for several sites are not cooperating. # These sites include "Skilak Lake Outflow," and "Jim's Landing # After investigating possible reasons, (white spaces, etc) no diagnoses are evident. # For time efficiency, we will instead manually rectify this in script with mutate and case_when sgs23 %<>% transform(monitoring_location_id = as.character(monitoring_location_id)) %>% # Jim's Landing mutate(monitoring_location_id = case_when( grepl("JIM'S|Jim's",sample_id) ~ "10000031", TRUE ~ monitoring_location_id)) %>% # Skilak Outlet mutate(monitoring_location_id = case_when( grepl("Skilak|SKILAK",sample_id) ~ "10000030", TRUE ~ monitoring_location_id)) # all 2023 SGS data joined and present in same dataframe ############### Part A.3: Assign miscellaneous calculated columns for 2023 SGS data (needed for next steps) ############### # Activity Type # method blank, trip blank, field duplicate) # this column is addressed out of left to right sequence because its content is needed to generate Activity ID sgs23 %<>% # designate special activity types mutate(activity_type = case_when( grepl("DUP|Dup", sample_id) ~ "Quality Control Field Replicate Msr/Obs", grepl("FB", sample_id) ~ "Quality Control Sample-Field Blank", grepl("TRIP BLANK", sample_id) ~ "Quality Control Sample-Trip Blank", TRUE ~ "")) %>% # designate all other project samples as project samples (using CDX domain value choices) mutate(activity_type = case_when( sample_type == "PS" & activity_type == "" ~ "Field Msr/Obs", TRUE ~ activity_type)) %>% # designate activity type abbreviations for late use in actvity ID mutate(activity_type_abbrv = case_when( activity_type == "Quality Control Field Replicate Msr/Obs" ~ "DUP", activity_type == "Quality Control Sample-Field Blank" ~ "FB", activity_type == "Quality Control Sample-Trip Blank" ~ "TB", TRUE ~ "" )) # Result Sample Fraction # CDX domains: "Filtered, field," "Filtered, lab," "Unfiltered," "Volatile", "Total" sgs23 %<>% mutate(result_sample_fraction = case_when( grepl("EP200.8", analytical_method) ~ "Filtered, lab", # dissolved metals grepl("EPA 200.7|SW846 6010D", analytical_method) ~ "Unfiltered", # total metals grepl("SW8260D", analytical_method) ~ "Volatile", # hydrocarbons grepl("SM21 4500NO3-F|SM21 4500P-B,E", analytical_method) ~ "Total",# total N and P TRUE ~ "")) %>% # designate dissolved vs. total metals (filtered vs. unfiltered) for use in concatenating Activity ID mutate(diss_abbrv = case_when( result_sample_fraction == "Filtered, lab" ~ "Diss", TRUE ~ "")) ``` ```{r message = FALSE, echo = F, include = F} ################### Part A.4: Proceed left to right across column names to calculate or assign as needed ################### # For the remainder of steps, we will conform our data to the columns present in the spreadsheet successfully uploaded with 2021 data proceeding left to right. Columns content is created in one of the following manners: # Static - column is assigned a static value # Concatenated - column combines static info from multiple columns # Calculated - column is dependent on measurements # NOTE: column names marked with triple asterisk "***" are common to all dataframes, and thus are done in bulk at the end of steps that are not common to all dataframes # Project ID (static)*** # Monitoring Location ID (static, already present) # Activity Media Name (static)*** # Activity Media Subdivision Name (static)*** # Activity ID (concatenated) (unique identifier) (limit of X characters) # due to CDX character limitations, we need to use abbreviations for 1) characteristic (analyte) and 2) sample type # import analyte abbreviations and join # note that surrogate organic compunds ("surr" suffix) do not have analyte abbreviations; becuase they are not project analytes analyte_abbrv <- read.csv("other/input/2023/misc/analytes_list_manual_edit.csv") %>% filter(analyte_abbreviation != "") colnames(analyte_abbrv) <- c("analyte","analyte_abbrv") sgs23 %<>% left_join(analyte_abbrv) # create Activity ID column sgs23 %<>% mutate(activity_id = case_when( activity_type_abbrv == "" ~ paste0(site_id,"-",collect_date,"-",analyte_abbrv), TRUE ~ paste0(site_id,"-",collect_date,"-",analyte_abbrv,"-",activity_type_abbrv))) %>% mutate(activity_id = case_when( diss_abbrv == "" ~ activity_id, TRUE ~ paste0(activity_id,"-",diss_abbrv))) # Activity Start Date (static, specific to SGS) sgs23 %<>% rename(activity_start_date = collect_date) # Activity Start Time (static, specific to SGS) sgs23 %<>% rename(activity_start_time = collect_time) # Activity Start Time Zone*** # Activity Depth/Height Measure *** # Activity Depth/Height Unit *** # Activity Comment *** # Activity Horizontal Collection Method *** # Activity Horizontal Reference Datum *** # Characteristic Name sgs23 %<>% rename(characteristic_name = analyte) # Result Analytical Method ID, and # Result Analytical Method Context # (Use table from CDX to match domain values to those names provided by SGS) # read in table analytical_methods_contexts <- read.csv("other/input/2023/misc/analytical_method_id_and_context_matching_table.csv") %>% select(-comments,-EPA.name) %>% filter(analytical_method != "") # join to sgs23 sgs23 %<>% left_join(analytical_methods_contexts) # Result Value sgs23 %<>% rename(result_value = result) # Result Unit sgs23 %<>% rename(result_unit = units) # Result Qualifier # options are "U" "N" "*N" "*" "=" "J". Modify to ensure choices match CDX domain options. # Note: *N" is not an option present in CDX; all other choices are. We will modify "*N" to simply "*" for simplification, for it's one instance as an MSD sample (matrix spike duplicate; non-field). Rationale: the "N" part of this qualifier is not mandatory information. sgs23 %<>% mutate(result_qualifier = case_when( resultflag == "*N" ~ "*", TRUE ~ resultflag)) # Result Weight Basis *** # Result Sample Fraction ## already completed in a previous step # Method Speciation ## only some parameters in CDX require that speciation is made explicit. Following example from 2021 experience sgs23 %<>% mutate(method_speciation = case_when( result_analytical_method_id == "4500-NO3(F)" ~ "as NO2+NO3", result_analytical_method_id == "4500-P-E" ~ "as P", TRUE ~ "")) # Result Value Type *** # Sample Collection Equipment Name *** # Result Detection Condition # categorical choice based on result qualifier. # Attempt to use matching table generates an erroneous many-to-many join for unknown reasons, so we will instead use "case_when" # result_detection_condition <- read.csv("other/input/2023/misc/result_detection_condition.csv") sgs23 %<>% mutate(result_detection_condition = case_when( result_qualifier == "U" ~ "Not Detected", result_qualifier == "J" ~ "Present Below Quantification Limit", result_qualifier == "N" ~ "Presumptive evidence of a nontarget compound", result_qualifier == "*" ~ "Sample was warm when received")) # Result Status ID (make as very last step?) # decision made after QA/QC process # TBD work to be completed here <-------------------------------------------- # Note: the acronyms & terminology for detection limits provided by SGS differ slightly from those required by CDX # Result Detection Limit Type 1 # Lower Quantitation Limit (listed as "loq", or "Limit of Quantitation") sgs23 %<>% mutate(result_detection_limit_type_1 = "Lower Quantitation Limit") # Result Detection Limit Value 1 sgs23 %<>% rename(result_detection_limit_value_1 = loq) # Result Detection Limit Unit 1 sgs23 %<>% mutate(result_detection_limit_unit_1 = result_unit) # Result Detection Limit Type 2 # Method Detection Level (listed as "lod", or "limit of detection") sgs23 %<>% mutate(result_detection_limit_type_2 = "Method Detection Level") # Result Detection Limit Value 2 sgs23 %<>% rename(result_detection_limit_value_2 = lod) # Result Detection Limit Unit 2 sgs23 %<>% mutate(result_detection_limit_unit_2 = result_unit) # Laboratory Name sgs23 %<>% mutate(laboratory_name = "SGS North America, Anchorage, Alaska") # Laboratory Comment Text # TBD sgs23 %<>% mutate(lab_comment_text = "") # Analysis Start Date sgs23 %<>% rename(analysis_start_date = run_date) # Analysis Start Time sgs23 %<>% rename(analysis_start_time = run_time) # Analysis Start Time Zone (can vary by year if subcontract lab used) sgs23 %<>% mutate(analysis_start_time_zone = "AKST") # Thermal Preservative Used *** # Sample Container Type sgs23 %<>% mutate(sample_container_type = case_when( result_analytical_method_id == "8260D" ~ "Glass Vial", TRUE ~ "Plastic Bottle")) # Sample Container Color sgs23 %<>% mutate(sample_container_color = case_when( result_analytical_method_id == "8260D" ~ "Amber", TRUE ~ "Opaque")) # Chemical Preservative Used # note that 200.8 samples were lab-filtered in spring & summer 2023, so no field preservative was used (would normally be HNO3 for year prior to 2022) sgs23 %<>% mutate(chemical_preservative_used = case_when( result_analytical_method_id == "8260D" ~ "HCl", result_analytical_method_id == "4500-NO3(F)" ~ "H2SO4", result_analytical_method_id == "4500-P-E" ~ "H2SO4", TRUE ~ "")) ``` --> use EPA metals guidance in QA/QC rather than at this stage! # check all these again, make decision if static, concatenated, or formulaic # general strategy: get all columns of sgs data to conform to final CDX format before joining. (e.g. do all sgs first, then swwtp) # there are pieces and parts existing in the 2021 script, but to stay organized it would make sense to proceed L --> R for each column ------------------------------------------------------------------------ ##### 2023 Fecal Coliform Lab Results (Soldotna Wastewater Treatment Plant Lab) ```{r message = FALSE, echo = F, include = F} ######################### Part B: SWWTP Fecal Coliform Data ############################ # spring spring_23_fc_dir <- "other/input/2023/spring_2023_wqx_data/Data/SWWTP/KRWF Fecal 05-02-23.xlsx" xfun::embed_file(spring_23_fc_dir, text = "Download Original Spring 2023 Fecal Coliform Lab Results from SWWTP") # summer summer_23_fc_dir <- "other/input/2023/summer_2023_wqx_data/Data/SWWTP/KRWF Fecal 07-18-23.xlsx" xfun::embed_file(summer_23_fc_dir, text = "Download Original Summer 2023 Fecal Coliform Lab Results from SWWTP") ``` Chain of custody documents available upon request. ```{r message = FALSE, echo = F, include = F} ############################ Part B.1: SWWTP Fecal Coliform Data Read In ############################# # spring spring_23_fc <- read_excel(spring_23_fc_dir, skip = 11, col_types = "text") %>% mutate(rec_date = spring23_sample_date) # summer summer_23_fc <- read_excel(summer_23_fc_dir, skip = 11, col_types = "text") %>% mutate(rec_date = summer23_sample_date) # join fc23 <- bind_rows(spring_23_fc,summer_23_fc) %>% clean_names() %>% mutate(time_sampled = format(as.POSIXct(Sys.Date() + as.numeric(time_sampled)), "%H:%M", tz="UTC"), time_in = format(as.POSIXct(Sys.Date() + as.numeric(time_in)), "%H:%M", tz="UTC"), time_out = format(as.POSIXct(Sys.Date() + as.numeric(time_out)), "%H:%M", tz="UTC")) ``` ```{r echo = F} ################### Part B.2: Create Consistent Sample Location Names for SWWTP FC data ##################### # execute once and manually modify csv to create match table # z <- sgs23 %>% # select(site_id,monitoring_location_id) %>% # distinct() # write.csv(z,"other/input/2023/misc/site_names_manual_edit_fc23.csv", row.names = F) # read in fc sites fc23_site_names <- read.csv("other/input/2023/misc/site_names_manual_edit_fc23.csv") %>% remove_empty() # join to fecal coliform data frame fc23 %<>% left_join(fc23_site_names) ############### Part B.3: Assign miscellaneous columns for 2023 SWWTP FC (needed for next steps) ############### # Activity Type # method blank, trip blank, field duplicate) # this column is addressed out of left to right sequence because its content is needed to generate Activity ID fc23 %<>% mutate(activity_type = case_when( grepl("DUP", sample_location_rm) ~ "Quality Control Field Replicate Msr/Obs", grepl("BLANK", sample_location_rm) ~ "Quality Control Sample-Lab Blank", grepl("POSITIVE", sample_location_rm) ~ "Sample-Positive Control", TRUE ~ "")) %>% # designate activity type abbreviations for late use in actvity ID mutate(activity_type_abbrv = case_when( activity_type == "Quality Control Field Replicate Msr/Obs" ~ "DUP", activity_type == "Quality Control Sample-Lab Blank" ~ "LB", activity_type == "Sample-Positive Control" ~ "POS", TRUE ~ "")) # Result Sample Fraction # CDX domains: none used in 2021 example fc23 %<>% mutate(result_sample_fraction = "") # Collect Date fc23 %<>% rename(collect_date = rec_date) ################### Part B.4: Proceed left to right across column names to calculate as needed ##################### # NOTE: column names marked with triple asterisk "***" are common to all dataframes, and thus are done in bulk at the end of steps that are not common to all dataframes # Project ID (static)*** # Monitoring Location ID (static, already present) # Activity Media Name (static)*** # Activity Media Subdivision Name (static)*** # Activity ID (concatenated) (unique identifier) (limit of X characters) # due to CDX character limitations, we need to use abbreviations for 1) characteristic (analyte) and 2) sample type fc23 %<>% mutate(analyte_abbrv = "FC") # create Activity ID column fc23 %<>% mutate(activity_id = case_when( grepl("POS|LB", activity_type_abbrv) ~ "", activity_type_abbrv == "" ~ paste0(site_id,"-",collect_date,"-",analyte_abbrv), TRUE ~ paste0(site_id,"-",collect_date,"-",analyte_abbrv,"-",activity_type_abbrv))) # Activity Start Date fc23 %<>% rename(activity_start_date = collect_date) # Activity Start Time (static, specific to SGS) fc23 %<>% rename(activity_start_time = time_sampled) # Activity Start Time Zone*** # Activity Depth/Height Measure *** # Activity Depth/Height Unit *** # Activity Comment *** # Activity Horizontal Collection Method *** # Activity Horizontal Reference Datum *** # Characteristic Name fc23 %<>% mutate(characteristic_name = "Fecal Coliform") # Result Analytical Method ID, and # Result Analytical Method Context fc23 %<>% mutate(result_analytical_method_id = "9222D", result_analytical_method_context = "APHA") # Result Value fc23 %<>% rename(result_value = colony_count_100m_l) # Result Unit fc23 %<>% mutate(result_unit = "cfu/100ml") # Result Qualifier # Use "=" if result > 1 and "U" if result < 1. See pg 17 of 2020 QAPP at "other/documents/QAPP/QAPP-2020-KenaiRiverWatershed_ZnCu.pdf" in Appendix A of comprehensive report fc23 %<>% mutate(result_qualifier = case_when( result_value < 1 ~ "U", TRUE ~ "=")) # Result Weight Basis *** # Result Sample Fraction ## already completed in a previous step # Method Speciation # not applicable to fecal coliform # Result Value Type *** # Sample Collection Equipment Name *** # Result Detection Condition # categorical choice based on result qualifier. # Attempt to use matching table generates an erroneous many-to-many join for unknown reasons, so we will instead use "case_when" # result_detection_condition <- read.csv("other/input/2023/misc/result_detection_condition.csv") fc23 %<>% mutate(result_detection_condition = case_when( result_qualifier == "U" ~ "Not Detected", result_qualifier == "J" ~ "Present Below Quantification Limit", TRUE ~ "")) # Result Status ID (make as very last step?) # decision made after QA/QC process # TBD work to be completed here <-------------------------------------------- # Note: the acronyms & terminology for detection limits provided by SGS differ slightly from those required by CDX # Result Detection Limit Type 1 # Lower Quantitation Limit (listed as "loq", or "Limit of Quantitation") fc23 %<>% mutate(result_detection_limit_type_1 = "Lower Quantitation Limit") # Result Detection Limit Value 1 fc23 %<>% mutate(result_detection_limit_value_1 = 1) # Result Detection Limit Unit 1 fc23 %<>% mutate(result_detection_limit_unit_1 = result_unit) # Result Detection Limit Type 2 # Method Detection Level (listed as "lod", or "limit of detection") fc23 %<>% mutate(result_detection_limit_type_2 = "") # Result Detection Limit Value 2 fc23 %<>% mutate(result_detection_limit_value_2 = "") # Result Detection Limit Unit 2 fc23 %<>% mutate(result_detection_limit_unit_2 = result_unit) # Laboratory Name fc23 %<>% mutate(laboratory_name = "Soldotna Wastewater Treatment Plant, Soldotna, Alaska") # Laboratory Comment Text # TBD fc23 %<>% mutate(lab_comment_text = notes) # Analysis Start Date # all FC samples are analyzed the same day as received by the lab fc23 %<>% rename(analysis_start_date = activity_start_date) # Analysis Start Time fc23 %<>% rename(analysis_start_time = time_in) # Analysis Start Time Zone (can vary by year if subcontract lab used) fc23 %<>% mutate(analysis_start_time_zone = "AKST") # Thermal Preservative Used *** # Sample Container Type fc23 %<>% mutate(sample_container_type = "Plastic Bottle") # Sample Container Color fc23 %<>% mutate(sample_container_color = "Clear") # Chemical Preservative Used fc23 %<>% mutate(chemical_preservative_used = "") ``` ##### 2023 Total Suspended Solids Lab Results (Soldotna Wastewater Treatment Plant (SWWTP)) ```{r, echo = F} ############################ Part C: SWWTP Fecal Coliform Data ############################# # read in # spring spring_23_tss_dir <- "other/input/2023/spring_2023_wqx_data/Data/SWWTP/KRWF TSS MONITORING 05-02-23.xlsx" xfun::embed_file(spring_23_tss_dir, text = "Download Original Spring 2023 Total Suspended Solids Results from SWWTP") # summer summer_23_tss_dir <- "other/input/2023/summer_2023_wqx_data/Data/SWWTP/KRWF TSS MONITORING 07-18-23.xlsx" xfun::embed_file(spring_23_tss_dir, text = "Download Original Summer 2023 Total Suspended Solids Results from SWWTP") ``` Chain of custody documents available upon request ```{r, echo = F} # read in 2023 TSS data # spring spring_23_tss <- read_excel(spring_23_tss_dir, sheet = "Updated_Formatting", skip = 1, col_types = "text") %>% mutate(rec_time = as_hms("12:44:00"), rec_date = spring23_sample_date) # summer summer_23_tss <- read_excel(summer_23_tss_dir, sheet = "Updated_Formatting", skip = 1, col_types = "text") %>% mutate(rec_time = as_hms("12:25:00"), rec_date = summer23_sample_date,) # join tss23 <- bind_rows(spring_23_tss,summer_23_tss) %>% # clean up names and formats clean_names() %>% transform(field_sample_date = excel_numeric_to_date(as.numeric(field_sample_date)), date_of_analysis = excel_numeric_to_date((as.numeric(date_of_analysis)))) %>% mutate(time_sampled = format(as.POSIXct(Sys.Date() + as.numeric(sample_time)), "%H:%M", tz="UTC"), time_analysis = format(as.POSIXct(Sys.Date() + as.numeric(time)), "%H:%M", tz="UTC")) %>% select(-sample_time,-time,-qc1,-data_entry,-qc2) ``` ```{r echo = F} ################### Part C.2: Create Consistent Sample Location Names for SWWTP TSS data ##################### # execute once and manually modify csv to create match table #z <- tss23 %>% # select(sample_location) %>% # distinct() #write.csv(z,"other/input/2023/misc/site_names_manual_edit_tss23.csv", row.names = F) # read in tss sites tss23_site_names <- read.csv("other/input/2023/misc/site_names_manual_edit_tss23.csv") %>% remove_empty() # join site names to tss data frame tss23 %<>% left_join(tss23_site_names) ############### Part C.3: Assign miscellaneous calculated columns for 2023 SWWTP TSS (needed for next steps) ############### # Activity Type # method blank, field duplicate) # this column is addressed out of left to right sequence because its content is needed to generate Activity ID tss23 %<>% mutate(activity_type = case_when( grepl("DUP", sample_location) ~ "Quality Control Field Replicate Msr/Obs", grepl("BLANK", sample_location) ~ "Quality Control Sample-Lab Blank", TRUE ~ "")) %>% # designate activity type abbreviations for late use in actvity ID mutate(activity_type_abbrv = case_when( activity_type == "Quality Control Field Replicate Msr/Obs" ~ "DUP", activity_type == "Quality Control Sample-Lab Blank" ~ "LB", TRUE ~ "")) # Result Sample Fraction # CDX domains: none used in 2021 example tss23 %<>% mutate(result_sample_fraction = "") # Collect Date tss23 %<>% rename(collect_date = field_sample_date) ################### Part C.4: Proceed left to right across column names to calculate as needed ##################### # NOTE: column names marked with triple asterisk "***" are common to all dataframes, and thus are done in bulk at the end of steps that are not common to all dataframes # Project ID (static)*** # Monitoring Location ID (static, already present) # Activity Media Name (static)*** # Activity Media Subdivision Name (static)*** # Activity ID (concatenated) (unique identifier) (limit of X characters) # due to CDX character limitations, we need to use abbreviations for 1) characteristic (analyte) and 2) sample type tss23 %<>% mutate(analyte_abbrv = "TSS") # create Activity ID column tss23 %<>% mutate(activity_id = case_when( grepl("DUP", activity_type_abbrv) ~ paste0(site_id,"-",collect_date,"-",analyte_abbrv,"-",activity_type_abbrv), grepl("LB", activity_type_abbrv) ~ "", TRUE ~ paste0(site_id,"-",collect_date,"-",analyte_abbrv))) # Activity Start Date tss23 %<>% rename(activity_start_date = collect_date) # Activity Start Time (static, specific to SGS) tss23 %<>% rename(activity_start_time = time_sampled) # Activity Start Time Zone*** # Activity Depth/Height Measure *** # Activity Depth/Height Unit *** # Activity Comment *** # Activity Horizontal Collection Method *** # Activity Horizontal Reference Datum *** # Characteristic Name tss23 %<>% mutate(characteristic_name = "Total suspended solids") # Result Analytical Method ID, and # Result Analytical Method Context tss23 %<>% mutate(result_analytical_method_id = "2540-D", result_analytical_method_context = "APHA") # Result Value tss23 %<>% rename(result_value = s_s_mg_l) # Result Unit tss23 %<>% mutate(result_unit = "mg/L") # Result Qualifier tss23 %<>% mutate(result_qualifier = case_when( result_value < 1 & result_value > 0.31 ~ "J", result_value < 0.31 ~ "U")) # Result Weight Basis *** # Result Sample Fraction ## already completed in a previous step # Method Speciation # not applicable to TSS # Result Value Type *** # Sample Collection Equipment Name *** # Result Detection Condition # categorical choice based on result qualifier. # Attempt to use matching table generates an erroneous many-to-many join for unknown reasons, so we will instead use "case_when" # result_detection_condition <- read.csv("other/input/2023/misc/result_detection_condition.csv") tss23 %<>% mutate(result_detection_condition = case_when( result_qualifier == "U" ~ "Not Detected", result_qualifier == "J" ~ "Present Below Quantification Limit", TRUE ~ "")) # Result Status ID (make as very last step?) # decision made after QA/QC process # TBD work to be completed here <-------------------------------------------- # Note: the acronyms & terminology for detection limits provided by SGS differ slightly from those required by CDX # Result Detection Limit Type 1 # Lower Quantitation Limit (listed as "loq", or "Limit of Quantitation") tss23 %<>% mutate(result_detection_limit_type_1 = "Lower Quantitation Limit") # Result Detection Limit Value 1 tss23 %<>% mutate(result_detection_limit_value_1 = 1) # Result Detection Limit Unit 1 tss23 %<>% mutate(result_detection_limit_unit_1 = result_unit) # Result Detection Limit Type 2 # Method Detection Level (listed as "lod", or "limit of detection") tss23 %<>% mutate(result_detection_limit_type_2 = "Method Detection Level") # Result Detection Limit Value 2 tss23 %<>% mutate(result_detection_limit_value_2 = 0.31) # Result Detection Limit Unit 2 tss23 %<>% mutate(result_detection_limit_unit_2 = result_unit) # Laboratory Name tss23 %<>% mutate(laboratory_name = "Soldotna Wastewater Treatment Plant, Soldotna, Alaska") # Laboratory Comment Text # TBD tss23 %<>% mutate(lab_comment_text = notes) # Analysis Start Date # all FC samples are analyzed the same day as received by the lab tss23 %<>% rename(analysis_start_date = date_of_analysis) # Analysis Start Time tss23 %<>% rename(analysis_start_time = time_analysis) # Analysis Start Time Zone (can vary by year if subcontract lab used) tss23 %<>% mutate(analysis_start_time_zone = "AKST") # Thermal Preservative Used *** # Sample Container Type tss23 %<>% mutate(sample_container_type = "Plastic Bottle") # Sample Container Color tss23 %<>% mutate(sample_container_color = "Opaque") # Chemical Preservative Used tss23 %<>% mutate(chemical_preservative_used = "") ``` ```{r echo =F} # remove extraneous dataframes rm(fc23_site_names,sgs_sitenames,spring_23_fc,spring_23_tss,summer_23_fc,summer_23_tss,tss23_site_names) ``` ```{r echo = F} ################# Part D: Join Separate Datasets into shared dataframe (SGS, Fecal Coliform, TSS) ################### # strategy - 1) retain only needed columns (2021 example), then 2) assign consistent column classes (temp loggers example) # subset dataframes to limited set of desired final colnames based on 2021 example # read in column names col_names <- read.csv("other/output/example_output/results_activities_2021.csv") %>% clean_names() %>% colnames() # retain desired columns from each dataframe # and, convert all columns to character sgs23 %<>% select(one_of(col_names)) %>% mutate(across(everything(), as.character)) fc23 %<>% select(one_of(col_names)) %>% mutate(across(everything(), as.character)) tss23 %<>% select(one_of(col_names)) %>% mutate(across(everything(), as.character)) # combine dat <- bind_rows(sgs23,fc23,tss23) ``` ```{r echo = F} ##################### Part E: Assign Information Common to All 2023 Datasets ######################### # Static additions to overall combined dataset (marked as *** in above steps) # Note: will need to replace all "sgs23" with "dat" # Project ID (static)*** dat %<>% mutate(project_id = "10000007") # Activity Media Name (static)*** dat %<>% mutate(activity_media_name = "Water") # Activity Media Subdivision Name (static)*** dat %<>% mutate(activity_media_subdivision_name = "Surface Water") # Activity Start Time Zone*** dat %<>% mutate (activity_start_time_zone = "AKST") # Activity Latitude *** # Activity Longitude *** # read in unqiue lat/long by monitoring location id lat_long <- read_csv("other/input/2023/misc/lat_long.csv") %>% transform(monitoring_location_id = as.character(monitoring_location_id)) # prep overall dataframe for join, then join dat %<>% mutate(monitoring_location_id = str_trim(monitoring_location_id)) %>% left_join(lat_long) # Activity Depth/Height Measure *** dat %<>% mutate(activity_depth_height_measure = "15") # Activity Depth/Height Unit *** dat %<>% mutate(activity_depth_height_unit = "cm") # Activity Comment *** dat %<>% mutate(activity_comment = "") # Activity Horizontal Collection Method *** dat %<>% mutate(activity_horizontal_collection_method = "GPS-Unspecified") # Activity Horizontal Reference Datum *** dat %<>% mutate(activity_horizontal_reference_datum = "NAD83") # Result Weight Basis *** dat %<>% mutate(result_weight_basis = "Sampled") # Result Value Type *** dat %<>% mutate(result_value_type = "Actual") # Sample Collection Equipment Name *** dat %<>% mutate(sample_collection_equipment_name = "Water Bottle") # Thermal Preservative Used *** dat %<>% mutate(thermal_preservative_used = "Cold Packs (4 deg C)") ``` ```{r echo = F} # transform column classes as necessary for QA/QC analyses ``` ### 2023 Data QA/QC Evaluation Prior to upload to the EPA WQX, all water quality data must be checked against a series of standard questions in order to evaluate how quality assurance / quality check (QA/QC) requirements are met. The draft Data Evaluation Checklist Template (available for download below) outlines these questions: ```{r, echo = F} xfun::embed_file('other/misc/qa_qc/Kenai_Baseline_Data_Evaluation_Checklist_2023.xlsx', text = "Download 2023 Draft Kenai Baseline Data Evaluation Checklist Template") ``` Download results pre-QA/QC; including lab QA QC info #### Pre-database ##### Overall project success **1.) Were the appropriate analytical methods used for all parameters?** Yes. Analytical methods from the approved 2020 QAPP were employed. **2.) Were there any deviations from the sampling plan?** Yes. One site in spring 2023 could not be accessed for sampling due to low water conditions (Kenai River main stem, RM 10.1). **3.) Were field duplicates, blanks, and/or other QC samples collected as planned?** ```{r echo = F} z <- dat %>% group_by(activity_start_date,activity_type,characteristic_name,result_analytical_method_id) %>% count() # WORKING HERE 2/26/24 # need to fix: for method 200.8, result_analytical_method_id and result_analytical_method_context ``` ```{r} ### Part E: QA/QC # question to add to existing list: # # Are sample collection dates unique to the actual recorded dates? # (no; spring has two dates; lab typo) # are fecal coliform "0" values listed as "NA"? (they should be) ```